package com.data.center.util;

import cn.hutool.core.io.FileUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.ListUtils;
import com.data.center.dto.OrganizationDto;
import com.data.center.exception.exception.BizException;
import com.data.center.exception.info.CommonEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author Li_Jx
 * @description: 将excel中的每一行以英文逗号为间隔存入list，前提excel各项数据非空，如果为空请参考注释掉的代码
 * @date 2023/9/9 15:03
 */
@Slf4j
public class ExcelToListUtils<T> {

    public List<T> excelToList(String filePath, Class<T> clazz) {

        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        List<T> resultList = new ArrayList<>();
        EasyExcel.read(filePath, clazz, new ReadListener<T>() {
            @Override
            public void invoke(T data, AnalysisContext context) {
                resultList.add(data);
            }
            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                // 输出数据日志
                log.info("{}数据，转化完成！", context);
            }
        }).sheet().doRead();

        return resultList;
    }

    public static List<String> parseExcel(String filePath) {
        List<String> stringList = new ArrayList<>();
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0); // 假设数据在第一个工作表中

            Iterator<Row> rowIterator = sheet.iterator();
            DataFormatter dataFormatter = new DataFormatter();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                StringBuilder sb = new StringBuilder();
                /* 当输入的excel存在空值，应考虑此种解决方式，需明确数组的列数
                Cell cell1 = row.getCell(0); // 第一列是 code
                Cell cell2 = row.getCell(1); // 第二列是 name
                String code = cell1 == null ? "null" : cell1.toString();
                String name = cell2 == null ? "null" : cell2.toString();
                sb.append(code).append(",").append(name);
                */
                for (Cell cell : row) {
                    if(cell == null) {
                        sb.append("null");
                    }
                    else{
                        sb.append(dataFormatter.formatCellValue(cell));
                    }
                    sb.append(",");
                }
                sb.deleteCharAt(sb.length() - 1); // 删除最后一个多余的逗号

                stringList.add(sb.toString()); // 将字符串添加到链表中

            }

        } catch (IOException e) {
            throw new BizException(CommonEnum.FILE_NOT_FOUND);
        }

        return stringList;
    }


    /*
    * 读取一个excel文件中某一列的数据，转换为List
    *
    * */
    public static List<String> excelColToList(String url, String sheetName, int colNum) {

        //通过sheet名获取
        ExcelReader reader = ExcelUtil.getReader(FileUtil.file(url), sheetName);
        // 读取并输出每一行的数据
        List<String> rows = new ArrayList<>();
        // 读取第一列数据（假设是A列）
        reader.readColumn(colNum, 0).forEach(item ->  rows.add(item.toString())); // 从行0开始读取该列数据
        reader.close();

        return rows;
    }



    /*
     * 读取一个excel文件的数据，以行为单位，转换为OrganizationDto对象
     *
     * */
    public static List<OrganizationDto> mapExcelToObjects(String filePath) {
        List<OrganizationDto> organizationDtoList = new ArrayList<>();

        ExcelReader reader = ExcelUtil.getReader(new File(filePath));
        List<List<Object>> excelData = reader.read();
        excelData = excelData.subList(1, excelData.size()); // 裁剪掉首行，首行为名称提示行
        for (List<Object> row : excelData) {
            OrganizationDto organizationDto = new OrganizationDto();

            organizationDto.setName(getStringValue(row, 0)); // 列索引0对应企业名称
            organizationDto.setAbbreviation(getListValue(row, 1)); // 列索引1对应单个企业简称
            organizationDto.setCategory(getStringValue(row, 2)); // 列索引2对应企业类型
            organizationDto.setUscc(getStringValue(row, 3)); // 列索引3对应企业USCC
            organizationDto.setRepresentative(getStringValue(row, 4)); // 列索引4对应企业法人
            organizationDto.setDescription(getStringValue(row, 5)); // 列索引5对应企业描述
            organizationDto.setCountry(getStringValue(row, 6)); // 列索引6对应企业国家
            organizationDto.setDivisionCode(getStringValue(row, 7)); // 列索引7对应企业区域编码
            organizationDto.setAddress(getStringValue(row, 8)); // 列索引8对应企业详细地址
            organizationDto.setPostCode(getStringValue(row, 9)); // 列索引9对应企业邮编

            organizationDtoList.add(organizationDto);
        }
        return organizationDtoList;
    }

    // 辅助方法，用于获取字符串值或处理空值
    private static String getStringValue(List<Object> row, int index) {
        return (row.size() > index && row.get(index) != null) ? row.get(index).toString() : "";
    }

    // 辅助方法，用于获取列表值或处理空值
    private static List<String> getListValue(List<Object> row, int index) {
        List<String> listValue = new ArrayList<>();
        if (row.size() > index && row.get(index) != null) {
            listValue.add(row.get(index).toString());
        }
        return listValue;
    }

    public static void writeObjectsToExcel(Map<OrganizationDto, String> organizationDtoList, String filePath) {
        try (Workbook workbook = new XSSFWorkbook()) { // 创建一个XLSX格式的工作簿
            Sheet sheet = workbook.createSheet("neoOrganization Data"); // 创建一个名为"neoOrganization Data"的工作表

            // 创建表头行
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("企业名称");
            headerRow.createCell(1).setCellValue("单个企业简称");
            headerRow.createCell(2).setCellValue("企业类型");
            headerRow.createCell(3).setCellValue("企业USCC");
            headerRow.createCell(4).setCellValue("企业法人");
            headerRow.createCell(5).setCellValue("企业描述");
            headerRow.createCell(6).setCellValue("企业国家");
            headerRow.createCell(7).setCellValue("企业区域编码");
            headerRow.createCell(8).setCellValue("企业详细地址");
            headerRow.createCell(9).setCellValue("企业邮编");

            headerRow.createCell(10).setCellValue("错误原因");
            // 遍历OrganizationDto列表，将数据写入工作表
            AtomicInteger i = new AtomicInteger(1);
            organizationDtoList.forEach((organizationDto, error) -> {
                Row row = sheet.createRow(i.getAndAdd(1)); // 创建新行

                // 设置每一列的数据
                row.createCell(0).setCellValue(organizationDto.getName());
                row.createCell(1).setCellValue(organizationDto.getAbbreviation().toString());
                row.createCell(2).setCellValue(organizationDto.getCategory());
                row.createCell(3).setCellValue(organizationDto.getUscc());
                row.createCell(4).setCellValue(organizationDto.getRepresentative());
                row.createCell(5).setCellValue(organizationDto.getDescription());
                row.createCell(6).setCellValue(organizationDto.getCountry());
                row.createCell(7).setCellValue(organizationDto.getDivisionCode());
                row.createCell(8).setCellValue(organizationDto.getAddress());
                row.createCell(9).setCellValue(organizationDto.getPostCode());

                row.createCell(10).setCellValue(error);
            });

            // 将工作簿保存到指定路径
            try (FileOutputStream outputStream = new FileOutputStream(filePath)) {
                workbook.write(outputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }



}
